package com.northpool.operator.statistics.dataset;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONPath;
import com.alibaba.fastjson.JSONValidator;
import com.northpool.operator.annotation.InputParam;
import com.northpool.operator.annotation.InputParams;
import com.northpool.operator.annotation.OperatorInfo;
import com.northpool.operator.annotation.OutputParam;
import com.northpool.operator.annotation.OutputParams;
import com.northpool.operator.common.algorithm.OperatorTestBuild;
import com.northpool.operator.common.algorithm.TestParamBuilder;
import com.northpool.operator.common.database.DatabaseUtils;
import com.northpool.operator.param.ParamTool;
import com.northpool.operator.type.TypeEnum;
import com.northpool.operator.utils.StpOperatorHelper;
import com.northpool.resources.datasource.IDataSource;
import com.northpool.resources.datasource.db.DbDataSource;
import org.apache.commons.lang3.StringUtils;

import java.util.LinkedHashSet;
import java.util.Set;

@OperatorInfo(
        ref = "Json2DbOperator",
        name = "JSON数据入库",
        version = "1.1.0",
        description = "单层JSON数据入库，不支持多层的JSON结构"
)
@OutputParams({
        @OutputParam(ref = "dataset", name = "结果数据集"),
        @OutputParam(ref = "dataSourceId", name = "数据源ID"),
        @OutputParam(ref = "tableName", name = "结果表名称"),
})
public class Json2DbOperatorV2 extends StpOperatorHelper {
    String dataSourceId;
    String tableName;
    String json;
    Boolean replaceTable;

    @InputParams({
            @InputParam(ref = "dataSourceId", name = "数据源ID", desc = "数据源ID", type = TypeEnum.DATASOURCE, required = true, testValue = "storage"),
            @InputParam(ref = "tableName", name = "数据表名称", desc = "数据表名称，如: public.table", type = TypeEnum.STRING, required = true, testValue = "result.结果入库测试"),
            @InputParam(ref = "replaceTable", name = "是否重建表", desc = "如果存在是否重建表", type = TypeEnum.BOOL, required = true,value = "false", testValue = "true"),
            @InputParam(ref = "json", name = "JSON字符串", required = true, desc = "JSON字符串数组，示例：{\"data\":[{\"name\":\"旱地\"},{\"name\":\"公园与绿地\"},{\"name\":\"广场用地\"},{\"name\":\"水浇地\"}],\"links\":[{\"source\":\"广场用地\",\"value\":1.46077857576266E-6,\"target\":\"旱地\"},{\"source\":\"公园与绿地\",\"value\":2.11407801716164E-7,\"target\":\"水浇地\"}],\"type\":\"sankey\"}"),
            @InputParam(ref = "jsonPath", name = "JSONPath表达式", desc = "JSONPath提取数组: $.links", required = true),
    })
    public Json2DbOperatorV2() {
    }

    public static void main(String[] args) throws Exception {
        OperatorTestBuild.execute(Json2DbOperatorV2.class,
                TestParamBuilder.aBuilder()
                        .param("dataSourceId", "402881ac934fb41701934fc212af000b")
                        .param("tableName", "result.图斑变更结果")
                        .param("replaceTable", "true")
                        .param("json", "[{\"_year__\":\"2000年\",\"dlmc\":\"沟渠\",\"dlbm\":\"1107\",\"_area__\":\"815.8192487565188\",\"geom\":\"{\\\"type\\\":\\\"Polygon\\\",\\\"coordinates\\\":[[[113.2254028787842,28.41949496288678],[113.22539841821425,28.419617823789586],[113.2254892341455,28.419859559837647],[113.22548971717715,28.419861057240386],[113.22555648899214,28.420108344829643],[113.22555679422271,28.42010970510596],[113.22558904524718,28.420289307149194],[113.22564377399875,28.420393330810366],[113.225645258839,28.420397296205234],[113.22568139523108,28.420552195862513],[113.22568147175187,28.42055255202868],[113.225726884035,28.420769094150664],[113.22572697748805,28.42076959992016],[113.22575961910852,28.420954324864567],[113.22582121703259,28.42125163722163],[113.22583667917095,28.42128345710486],[113.22582414599398,28.42128575515202],[113.22579879606668,28.421291955006794],[113.225796973805,28.42129318087877],[113.2257828002297,28.421264012931708],[113.22578147616392,28.421260141912033],[113.22571941609355,28.420960594127333],[113.22571933011972,28.420960127571963],[113.22568669195405,28.420775422763757],[113.22564137209136,28.42055930993621],[113.22560575702425,28.42040664491163],[113.22555082711868,28.42030223979215],[113.22554917499869,28.420297458046317],[113.22551659917573,28.420116048856826],[113.22545021419042,28.419870195807796],[113.22535853079415,28.419626146778036],[113.22535750025014,28.419619880880802],[113.22536198995957,28.419496228917858],[113.22538670200406,28.41949921413419],[113.2254028787842,28.41949496288678]]],\\\"crs\\\":{\\\"type\\\":\\\"name\\\",\\\"properties\\\":{\\\"name\\\":\\\"EPSG:4326\\\"}}}\"},{\"_year__\":\"2001年\",\"dlmc\":\"沟渠\",\"dlbm\":\"1107\",\"_area__\":\"815.8192487565188\",\"geom\":\"{\\\"type\\\":\\\"Polygon\\\",\\\"coordinates\\\":[[[113.2254028787842,28.41949496288678],[113.22539841821425,28.419617823789586],[113.2254892341455,28.419859559837647],[113.22548971717715,28.419861057240386],[113.22555648899214,28.420108344829643],[113.22555679422271,28.42010970510596],[113.22558904524718,28.420289307149194],[113.22564377399875,28.420393330810366],[113.225645258839,28.420397296205234],[113.22568139523108,28.420552195862513],[113.22568147175187,28.42055255202868],[113.225726884035,28.420769094150664],[113.22572697748805,28.42076959992016],[113.22575961910852,28.420954324864567],[113.22582121703259,28.42125163722163],[113.22583667917095,28.42128345710486],[113.22582414599398,28.42128575515202],[113.22579879606668,28.421291955006794],[113.225796973805,28.42129318087877],[113.2257828002297,28.421264012931708],[113.22578147616392,28.421260141912033],[113.22571941609355,28.420960594127333],[113.22571933011972,28.420960127571963],[113.22568669195405,28.420775422763757],[113.22564137209136,28.42055930993621],[113.22560575702425,28.42040664491163],[113.22555082711868,28.42030223979215],[113.22554917499869,28.420297458046317],[113.22551659917573,28.420116048856826],[113.22545021419042,28.419870195807796],[113.22535853079415,28.419626146778036],[113.22535750025014,28.419619880880802],[113.22536198995957,28.419496228917858],[113.22538670200406,28.41949921413419],[113.2254028787842,28.41949496288678]]],\\\"crs\\\":{\\\"type\\\":\\\"name\\\",\\\"properties\\\":{\\\"name\\\":\\\"EPSG:4326\\\"}}}\"},{\"_year__\":\"2002年\",\"dlmc\":\"沟渠\",\"dlbm\":\"1107\",\"_area__\":\"815.8192487565188\",\"geom\":\"{\\\"type\\\":\\\"Polygon\\\",\\\"coordinates\\\":[[[113.2254028787842,28.41949496288678],[113.22539841821425,28.419617823789586],[113.2254892341455,28.419859559837647],[113.22548971717715,28.419861057240386],[113.22555648899214,28.420108344829643],[113.22555679422271,28.42010970510596],[113.22558904524718,28.420289307149194],[113.22564377399875,28.420393330810366],[113.225645258839,28.420397296205234],[113.22568139523108,28.420552195862513],[113.22568147175187,28.42055255202868],[113.225726884035,28.420769094150664],[113.22572697748805,28.42076959992016],[113.22575961910852,28.420954324864567],[113.22582121703259,28.42125163722163],[113.22583667917095,28.42128345710486],[113.22582414599398,28.42128575515202],[113.22579879606668,28.421291955006794],[113.225796973805,28.42129318087877],[113.2257828002297,28.421264012931708],[113.22578147616392,28.421260141912033],[113.22571941609355,28.420960594127333],[113.22571933011972,28.420960127571963],[113.22568669195405,28.420775422763757],[113.22564137209136,28.42055930993621],[113.22560575702425,28.42040664491163],[113.22555082711868,28.42030223979215],[113.22554917499869,28.420297458046317],[113.22551659917573,28.420116048856826],[113.22545021419042,28.419870195807796],[113.22535853079415,28.419626146778036],[113.22535750025014,28.419619880880802],[113.22536198995957,28.419496228917858],[113.22538670200406,28.41949921413419],[113.2254028787842,28.41949496288678]]],\\\"crs\\\":{\\\"type\\\":\\\"name\\\",\\\"properties\\\":{\\\"name\\\":\\\"EPSG:4326\\\"}}}\"},{\"_year__\":\"2003年\",\"dlmc\":\"沟渠\",\"dlbm\":\"1107\",\"_area__\":\"815.8192487565188\",\"geom\":\"{\\\"type\\\":\\\"Polygon\\\",\\\"coordinates\\\":[[[113.2254028787842,28.41949496288678],[113.22539841821425,28.419617823789586],[113.2254892341455,28.419859559837647],[113.22548971717715,28.419861057240386],[113.22555648899214,28.420108344829643],[113.22555679422271,28.42010970510596],[113.22558904524718,28.420289307149194],[113.22564377399875,28.420393330810366],[113.225645258839,28.420397296205234],[113.22568139523108,28.420552195862513],[113.22568147175187,28.42055255202868],[113.225726884035,28.420769094150664],[113.22572697748805,28.42076959992016],[113.22575961910852,28.420954324864567],[113.22582121703259,28.42125163722163],[113.22583667917095,28.42128345710486],[113.22582414599398,28.42128575515202],[113.22579879606668,28.421291955006794],[113.225796973805,28.42129318087877],[113.2257828002297,28.421264012931708],[113.22578147616392,28.421260141912033],[113.22571941609355,28.420960594127333],[113.22571933011972,28.420960127571963],[113.22568669195405,28.420775422763757],[113.22564137209136,28.42055930993621],[113.22560575702425,28.42040664491163],[113.22555082711868,28.42030223979215],[113.22554917499869,28.420297458046317],[113.22551659917573,28.420116048856826],[113.22545021419042,28.419870195807796],[113.22535853079415,28.419626146778036],[113.22535750025014,28.419619880880802],[113.22536198995957,28.419496228917858],[113.22538670200406,28.41949921413419],[113.2254028787842,28.41949496288678]]],\\\"crs\\\":{\\\"type\\\":\\\"name\\\",\\\"properties\\\":{\\\"name\\\":\\\"EPSG:4326\\\"}}}\"},{\"_year__\":\"2023年\",\"dlmc\":\"沟渠\",\"dlbm\":\"1107\",\"_area__\":\"815.8192487565188\",\"geom\":\"{\\\"type\\\":\\\"Polygon\\\",\\\"coordinates\\\":[[[113.2254028787842,28.41949496288678],[113.22539841821425,28.419617823789586],[113.2254892341455,28.419859559837647],[113.22548971717715,28.419861057240386],[113.22555648899214,28.420108344829643],[113.22555679422271,28.42010970510596],[113.22558904524718,28.420289307149194],[113.22564377399875,28.420393330810366],[113.225645258839,28.420397296205234],[113.22568139523108,28.420552195862513],[113.22568147175187,28.42055255202868],[113.225726884035,28.420769094150664],[113.22572697748805,28.42076959992016],[113.22575961910852,28.420954324864567],[113.22582121703259,28.42125163722163],[113.22583667917095,28.42128345710486],[113.22582414599398,28.42128575515202],[113.22579879606668,28.421291955006794],[113.225796973805,28.42129318087877],[113.2257828002297,28.421264012931708],[113.22578147616392,28.421260141912033],[113.22571941609355,28.420960594127333],[113.22571933011972,28.420960127571963],[113.22568669195405,28.420775422763757],[113.22564137209136,28.42055930993621],[113.22560575702425,28.42040664491163],[113.22555082711868,28.42030223979215],[113.22554917499869,28.420297458046317],[113.22551659917573,28.420116048856826],[113.22545021419042,28.419870195807796],[113.22535853079415,28.419626146778036],[113.22535750025014,28.419619880880802],[113.22536198995957,28.419496228917858],[113.22538670200406,28.41949921413419],[113.2254028787842,28.41949496288678]]],\\\"crs\\\":{\\\"type\\\":\\\"name\\\",\\\"properties\\\":{\\\"name\\\":\\\"EPSG:4326\\\"}}}\"}]")
                        .param("jsonPath", "$")
                        .build());
    }

    @Override
    public void execute(ParamTool paramTool) throws Exception {
        this.dataSourceId = paramTool.getStr("dataSourceId");
        this.tableName = paramTool.getStr("tableName");
        String jsonPath = paramTool.getStr("jsonPath");

        // 输出结果数据集
        this.result("tableName", this.tableName);
        this.result("dataSourceId", this.dataSourceId);
        this.result("dataset", DatabaseUtils.buildDatasetJson(this.dataSourceId, this.tableName));

        this.json = paramTool.getStr("json");
        this.replaceTable = paramTool.getBool("replaceTable");

        String dataSourceJson = DatabaseUtils.getDataSourceJson(this.dataSourceId);
        if (StringUtils.isBlank(dataSourceJson)) {
            throw new RuntimeException("数据源未找到： " + this.dataSourceId);
        }
        JSONArray arrVal;
        JSONValidator.Type type = JSONValidator.from(this.json).getType();
        if (JSONValidator.Type.Array.equals(type)) {
            arrVal = JSONArray.parseArray(this.json);
            if (arrVal.isEmpty()) {
                throw new RuntimeException("JSON数据为空");
            }
        } else if (JSONValidator.Type.Object.equals(type)){
            JSONObject object = JSONObject.parseObject(this.json);
            String arrValStr = JSONPath.eval(object, jsonPath).toString();
            boolean empty = object.isEmpty();
            if (empty) {
                throw new RuntimeException("JSON数据为空");
            }
            arrVal = JSONArray.parseArray(arrValStr);
        } else {
            throw new RuntimeException("JSON数据格式错误");
        }

        if (arrVal.isEmpty()) {
            throw new RuntimeException("JSONPath提取数组为空");
        }

        DbDataSource dbDataSource = (DbDataSource) IDataSource.fromJSON(dataSourceJson);
        if (this.replaceTable && dbDataSource.hasTable(this.tableName)) {
            dbDataSource.drop(this.tableName);
        }
        // 遍历字段并生成列定义
        JSONObject jsonObject = (JSONObject) arrVal.get(0);
        Set<String> fields = new LinkedHashSet<>();
        for (String key : jsonObject.keySet()) {
            fields.add(key);
        }

        if (tableName.contains(".")) {
            tableName = tableName.replace(".", "\".\"");
        }
        tableName = "\"" + tableName + "\"";

        StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        createTableSql.append(tableName).append(" (");

        for (String key : fields) {
            createTableSql.append("\"").append(key).append("\" text, ");
        }
        String sql = createTableSql.substring(0, createTableSql.length() - 2) + ")";
        dbDataSource.executeSql(sql);

        // 插入数据
        for (Object obj : arrVal) {
            JSONObject item = (JSONObject) obj;
            StringBuilder insertSql = new StringBuilder("INSERT INTO ").append(tableName).append(" (");

            // 添加字段名
            for (String field : fields) {
                insertSql.append("\"").append(field).append("\", ");
            }

            // 去掉最后一个逗号和空格
            insertSql.setLength(insertSql.length() - 2);
            insertSql.append(") VALUES (");

            // 添加字段值
            for (String key : item.keySet()) {
                insertSql.append("'").append(item.getString(key)).append("', ");
            }
            insertSql.setLength(insertSql.length() - 2);
            insertSql.append(")");

            dbDataSource.executeSql(insertSql.toString());
        }

        this.successExit();
    }

}
